# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import os
import sqlite3

project_path = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
db_file = os.path.join(project_path, 'data.db')


class InstitutionsPipeline:

    def open_spider(self, spider):
        db_name = spider.settings.get('SQLITE_DB_NAME', db_file)
        self.db_conn = sqlite3.connect(db_name)
        self.db_cur = self.db_conn.cursor()

    def process_item(self, item, spider):
        # self.insert_position(item)
        self.insert_summary(item)
        self.db_conn.commit()
        return item

    # 关闭数据库
    def close_spider(self, spider):
        self.db_conn.close()

        # 对数据进行处理

    def insert_position(self, item):
        values = (
            item['job_code'],
            item['manage_people'],
            item['manager_name'],
            item['plan_num'],
            item['plan_department'],
            item['specialized'],
            item['academic_qualifications'],
            item['degree'],
            item['age'],
            item['other'],
            item['common_categories'],
            item['common_code'],
            item['remark'],
            item['phone'],
            item['key_word']
        )

        sql = 'INSERT INTO ah_positions(job_code,manage_people,manager_name,plan_num,plan_department,specialized,academic_qualifications,degree,age,other,common_categories,common_code,remark,phone,key_word) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
        self.db_cur.execute(sql, values)

    def insert_summary(self, item):
        values = (item['job_code'],
                  item['manager_name'],
                  item['plan_department'],
                  item['plan_num'],
                  item['submit_num'],
                  item['qualified_num'],
                  item['payment_num'],
                  item['update_time']
                  )
        sql = 'INSERT INTO ah_summary(job_code,manager_name,plan_department,plan_num,submit_num,qualified_num,payment_num,update_time) VALUES(?,?,?,?,?,?,?,?)'
        self.db_cur.execute(sql, values)
